package com.qf.dao.impl;

import com.qf.dao.StudentDao;
import com.qf.pojo.Student;
import com.qf.service.StudentService;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class StudentDaoImpl implements StudentDao {
  private   static   Connection conn=null;
    //简单封装jdbc
      static {

          try {
              //注册驱动
              Class.forName("com.mysql.jdbc.Driver");
              //创建连接
                  conn= DriverManager.getConnection("jdbc:mysql://localhost:3306/sql_2104?characterEncoding=utf8","root","root");
              } catch (Exception e) {
                  e.printStackTrace();
              }

      }
      @Override
    public List<Student> findAll() {
          //创建sql
        String sql="select * from student";
        PreparedStatement ps=null;
        ResultSet rs=null;
        try {
            //预编译sql
            ps=conn.prepareStatement(sql);
            //处理结果集
            rs=ps.executeQuery();
            List<Student> list=new ArrayList<Student>();
            while (rs.next()){
                Student student=new Student();
                student.setSname(rs.getString("sname"));
                student.setAge(rs.getInt("age"));
                student.setSex(rs.getString("sex"));
                student.setTel(rs.getString("tel"));
                student.setEmail(rs.getString("email"));
                student.setAdress(rs.getString("adress"));
                list.add(student);
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                rs.close();
                ps.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return null;
    }
@Override
    public Integer insertStudent(Student student) {
          String sql="insert into student values(?,?,?,?,?,?)";
          PreparedStatement ps=null;
        try {
            ps=conn.prepareStatement(sql);
            ps.setObject(1,"sname");
            ps.setObject(2,"age");
            ps.setObject(3,"sex");
            ps.setObject(4,"tel");
            ps.setObject(5,"email");
            ps.setObject(6,"adress");
            int count=ps.executeUpdate();
            return count;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                ps.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
        return null;
    }

    @Override
    public Student findById(Integer id) {
        String sql ="select * from tb_student where id = ?";
        PreparedStatement ps=null;
        ResultSet rs=null;
        try {
            ps = conn.prepareStatement(sql);
            ps.setObject(1,id);
            rs= ps.executeQuery();
            Student student = new Student();
            while (rs.next()){
                student.setId(rs.getInt("id"));
                student.setSname(rs.getString("student_name"));
                student.setAge(rs.getInt("age"));
                student.setSex(rs.getString("sex"));
                student.setTel(rs.getString("tel"));
                student.setEmail(rs.getString("email"));
                student.setAdress(rs.getString("adress"));
            }
            return student;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                rs.close();
                ps.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }

        return null;
    }

    @Override
    public Integer deleteById(Integer id) {
        String sql ="delete from student where id =?";

        PreparedStatement ps = null;

        try {
            ps = conn.prepareStatement(sql);
            ps.setObject(1,id);
            int count = ps.executeUpdate();
            return count;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                ps.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }

        return 0;
    }

    @Override
    public Integer updateById(Student student) {
        String sql = "update student set sname=?,age=?,sex=?,tel=?,email=?,adress=? where id = ?";
        PreparedStatement ps = null;

        try {
            ps = conn.prepareStatement(sql);

            ps.setObject(1,student.getSname());
            ps.setObject(2,student.getAge());
            ps.setObject(3,student.getSex());
            ps.setObject(4,student.getSex());
            ps.setObject(5,student.getSex());
            ps.setObject(6,student.getAdress());
            ps.setObject(7,student.getId());

            int count = ps.executeUpdate();
            return count;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                ps.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }

        return 0;
    }

}

